﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DbCommon
{
    public class SQLHelper
    {
        //定义连接对象
        private static SqlConnection Sconn = null;
        #region 读取连接字符串
        //private static string _SConnectionStr = System.Configuration.ConfigurationSettings.AppSettings["DbConnection"].ToString());
        private static string _SConnectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
        #endregion

        #region SqlConnection
        public static string SConnectionStr
        {
            get { return SQLHelper._SConnectionStr; }
            set { SQLHelper._SConnectionStr = value; }
        }
        #endregion

        #region getSqlConnection
        /// <summary>
        /// 获取一个数据库连接对象
        /// </summary>
        /// <returns></returns>
        public static SqlConnection getSqlConnection()
        {
            try
            {
                return new SqlConnection(SConnectionStr);
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
        }
        #endregion

        #region GetRecordCount
        /// <summary>
        /// 获取某表中的总记录数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>表记录总数Object</returns>
        public static object GetRecordCount(string tableName,string sqlcon)
        {
            if (Sconn == null)
            {
                Sconn = getSqlConnection();
            }
            if (Sconn.State != ConnectionState.Open)
            {
                Sconn.Open();
            }
            string CommandText;
            if (string.IsNullOrEmpty(sqlcon))
                CommandText = string.Format("select count(*) from {0}", tableName);
            else
                CommandText = string.Format("select count(*) from {0} where {1}", tableName,sqlcon);
            SqlCommand cmd = new SqlCommand(CommandText, Sconn);
            try
            {
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                try
                {
                    Sconn.Close();
                }
                catch (Exception exception2)
                {
                    throw new Exception(exception2.Message);
                }
            }
        }
        #endregion

        #region ExecuteAsDataTable
        /// <summary>
        /// 执行指定sql语句，并返回DataTable对象
        /// </summary>
        /// <param name="CommandText">sql语句</param>
        /// <param name="Params">参数列表</param>
        /// <returns></returns>
        public static DataTable ExecuteAsDataTable(string CommandText, params Parameter[] Params)
        {
            if (Sconn == null) //如果连接对象为空,则新建个连接对象
            {
                Sconn = getSqlConnection();
            }
            if (Sconn.State != ConnectionState.Open)
            {
                Sconn.Open();
            }
            SqlDataAdapter adapter = new SqlDataAdapter("", Sconn);
            SqlCommand cmd = new SqlCommand(CommandText, Sconn);
            AddParameter(ref cmd, Params); //为sql语句填充参数
            adapter.SelectCommand = cmd;
            DataTable dataTable = new DataTable();
            bool flag = false;
            try
            {
                adapter.Fill(dataTable);
                flag = true;
            }
            catch
            {
                flag = false;
            }
            finally
            {
                try
                {
                    Sconn.Close();
                }
                catch (Exception exception2)
                {
                    throw new Exception(exception2.Message);
                }
            }
            return (flag ? dataTable : null);
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行指定sql语句
        /// </summary> 
        /// <param name="CommandText">sql语句</param>
        /// <param name="Params">参数列表</param>
        /// <returns>返回执行结果。小于零错误</returns>
        public static int ExecuteNonQuery(string CommandText, params Parameter[] Params)
        {
            if (Sconn == null)
            {
                Sconn = getSqlConnection();
            }
            if (Sconn.State != ConnectionState.Open)
            {
                Sconn.Open();
            }
            SqlCommand cmd = new SqlCommand(CommandText, Sconn);
            AddParameter(ref cmd, Params);
            SqlTransaction transaction = Sconn.BeginTransaction(); //在事务中执行sql语句
            cmd.Transaction = transaction;
            bool flag = false;
            try
            {
                cmd.ExecuteNonQuery();
                transaction.Commit();
                flag = true;
            }
            catch (Exception ex1)
            {
                transaction.Rollback();
                flag = false;
                throw new Exception(ex1.Message);
            }
            finally
            {
                try
                {
                    Sconn.Close();
                }
                catch (Exception exception2)
                {
                    throw new Exception(exception2.Message);
                }
            }
            return (flag ? 0 : -2);
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 执行指定sql语句，并返回第一行第一列值
        /// </summary>    
        /// <param name="CommandText">sql语句</param>
        /// <param name="Params">参数列表</param>
        /// <returns>返回一行一列值</returns>
        public static object ExecuteScalar(string CommandText, params Parameter[] Params)
        {
            if (Sconn == null)
            {
                Sconn = getSqlConnection();
            }
            if (Sconn.State != ConnectionState.Open)
            {
                Sconn.Open();
            }
            SqlCommand cmd = new SqlCommand(CommandText, Sconn);
            AddParameter(ref cmd, Params);
            try
            {
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                try
                {
                    Sconn.Close();
                }
                catch (Exception exception2)
                {
                    throw new Exception(exception2.Message);
                }
            }
        }
        #endregion

        #region ExecuteStoredProcedureWithQuery
        /// <summary>
        /// 执行指定存储过程(事务中),返回结果集
        /// </summary>
        /// <param name="StoredProcedureName">存储过程名称</param>
        /// <param name="Outputs">输出参数列表</param>
        /// <param name="Params">输入参数列表</param>
        /// <returns>返回查询结果集</returns>
        public static DataSet ExecuteStoredProcedureWithQuery(string StoredProcedureName, ref OutputParameter Outputs, params Parameter[] Params)
        {
            DataSet dataSet = new DataSet();
            if (Sconn == null)
            {
                Sconn = getSqlConnection();
            }
            if (Sconn.State != ConnectionState.Open)
            {
                Sconn.Open();
            }
            SqlDataAdapter adapter = new SqlDataAdapter("", Sconn);
            SqlCommand cmd = new SqlCommand(StoredProcedureName, Sconn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();//清除参数
            AddParameter(ref cmd, Params);//为sql语句添加参数  
            SqlTransaction transaction = Sconn.BeginTransaction(); //开始事务
            cmd.Transaction = transaction;
            adapter.SelectCommand = cmd;
            bool flag = false;
            try
            {
                adapter.Fill(dataSet);
                transaction.Commit();
                flag = true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                try
                {
                    Sconn.Close();
                }
                catch (Exception exception2)
                {
                    throw new Exception(exception2.Message);
                }
            }
            if (!flag)
            {
                return null;
            }
            AddReturnParameter(cmd, ref Outputs); //将输出参数添加到 Outputs中 以便上层获取输出参数值
            return dataSet;
        }
        #endregion

        #region ExecuteStoredProcedureNonQuery
        /// <summary>
        /// 执行指定存储过程(事务中),不返回结果集
        /// </summary>
        /// <param name="StoredProcedureName">存储过程名称</param>
        /// <param name="Outputs">输出参数列表</param>
        /// <param name="Params">输入参数列表</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteStoredProcedureNonQuery(string StoredProcedureName, ref OutputParameter Outputs, params Parameter[] Params)
        {
            if (Sconn == null)
            {
                Sconn = getSqlConnection();
            }
            if (Sconn.State != ConnectionState.Open)
            {
                Sconn.Open();
            }
            SqlCommand cmd = new SqlCommand(StoredProcedureName, Sconn);
            cmd.CommandType = CommandType.StoredProcedure;
            AddParameter(ref cmd, Params);
            SqlTransaction transaction = Sconn.BeginTransaction();
            cmd.Transaction = transaction;
            bool flag = false;
            try
            {
                cmd.ExecuteNonQuery();
                transaction.Commit();
                flag = true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                try
                {
                    Sconn.Close();
                }
                catch (Exception exception2)
                {
                    throw new Exception(exception2.Message);
                }
            }
            if (!flag)
            {
                return -2;
            }
            AddReturnParameter(cmd, ref Outputs);
            return 0;
        }
        #endregion

        #region Parameter
        /// <summary>
        /// 执行Sql语句的参数
        /// </summary>
        public class Parameter
        {
            public ParameterDirection Direction; //方向
            public string Name; //名称
            public int Size; //大小
            public DbType Type; //类型
            public object Value; //值

            public Parameter(string name, DbType type, int size, ParameterDirection direction, object value)
            {
                this.Name = name;
                this.Type = type;
                this.Size = size;
                this.Direction = direction;
                this.Value = value;
            }
        }
        #endregion

        #region AddParameter
        /// <summary>
        /// 为SqlCommand 对象添加参数
        /// </summary>
        /// <param name="Cmd">SqlCommand对象</param>
        /// <param name="Params">参数列表</param>
        private static void AddParameter(ref SqlCommand Cmd, params Parameter[] Params)
        {
            //如果SqlCommand对象和参数列表不为空,则进行添加参数操作
            if ((Params != null) && (Cmd != null))
            {
                for (int i = 0; i < Params.Length; i++)
                {
                    if (Params[i] != null) //判断参数是否为空
                    {
                        SqlParameter parameter = new SqlParameter();
                        parameter.ParameterName = Params[i].Name.StartsWith("@") ? Params[i].Name : ("@" + Params[i].Name); //哪果参数没有以"@"开头，则手动添加上"@"符号
                        parameter.SqlDbType = DbType2SqlDbType(Params[i].Type); //设置参数类型
                        if (Params[i].Size != 0)
                        {
                            parameter.Size = Params[i].Size; //设置参数大小
                        }
                        parameter.Direction = Params[i].Direction; //设置参数方向
                        //如果参数方向为"输入" 或者 "输入输出"并且参数不为空，则为参数的 Value属性赋值
                        if (((Params[i].Direction == ParameterDirection.InputOutput) || (Params[i].Direction == ParameterDirection.Input)) && (Params[i].Value != null))
                        {
                            parameter.Value = Params[i].Value;
                        }
                        Cmd.Parameters.Add(parameter); //将参数添加到SqlCommand命令中
                    }
                }
            }
        }
        #endregion

        #region OutputParameter
        /// <summary>
        /// 输出参数,用来获取执行Sql语句后的输出参数
        /// </summary>
        public class OutputParameter
        {
            public ArrayList ParametersName = new ArrayList(); //保存输出参数名称
            public ArrayList ParametersValue = new ArrayList();//保存输出参数值

            public void Add(string Name, object Value)
            {
                this.ParametersName.Add(Name.StartsWith("@") ? Name.Substring(1, Name.Length - 1) : Name);//去掉参数前的"@"符号
                this.ParametersValue.Add(Value);
            }

            //清除所有参数
            public void Clear()
            {
                this.ParametersName.Clear();
                this.ParametersValue.Clear();
            }
            //获取参数个数
            public int Count
            {
                get
                {
                    if (this.ParametersName == null)
                    {
                        return 0;
                    }
                    return this.ParametersName.Count;
                }
            }

            /// <summary>
            /// 获取参数值(按参数名称)
            /// </summary>
            /// <param name="Name">参数名称</param>
            /// <returns></returns>
            public object this[string Name]
            {
                get
                {
                    if (this.ParametersValue != null)
                    {
                        for (int i = 0; i < this.ParametersName.Count; i++)
                        {
                            if (this.ParametersName[i].ToString() == Name)
                            {
                                return this.ParametersValue[i];
                            }
                        }
                    }
                    return null;
                }
            }

            /// <summary>
            /// 获取参数值(按下标)
            /// </summary>
            /// <param name="Index">索引</param>
            /// <returns></returns>
            public object this[int Index]
            {
                get
                {
                    if (this.ParametersValue == null)
                    {
                        return null;
                    }
                    if (this.ParametersValue.Count == 0)
                    {
                        return null;
                    }
                    return this.ParametersValue[Index];
                }
            }
        }
        #endregion

        #region AddReturnParameter
        /// <summary>
        /// 从SqlCommand 对象获取输出参数值
        /// </summary>
        /// <param name="Cmd">SqlCommand对象</param>
        /// <param name="Outputs">输出参数对象(这里是传的引用)</param>
        private static void AddReturnParameter(SqlCommand Cmd, ref OutputParameter Outputs)
        {
            //如果SqlCommand对象和参数列表不为空,则进行获取参数操作
            if ((Cmd != null) && (Cmd.Parameters.Count != 0))
            {
                for (int i = 0; i < Cmd.Parameters.Count; i++)
                {
                    SqlParameter parameter = Cmd.Parameters[i];
                    //如果参数方向为"输出" 或者 "输入输出"并且参数不为空，则提取出参数的"ParameterName"与 "Value"属性的值，并添加到Outputs中
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Output)) || (parameter.Direction == ParameterDirection.ReturnValue))
                    {
                        Outputs.Add(parameter.ParameterName, parameter.Value);
                    }
                }
            }
        }
        #endregion

        #region 数据类型映射
        private static SqlDbType DbType2SqlDbType(DbType dbType)
        {
            switch (dbType)
            {
                case DbType.Boolean:
                    return SqlDbType.Bit;
                case DbType.Byte:
                    return SqlDbType.TinyInt;
                case DbType.Int16:
                    return SqlDbType.SmallInt;
                case DbType.Int32:
                    return SqlDbType.Int;
                case DbType.Int64:
                    return SqlDbType.BigInt;
                case DbType.Single:
                    return SqlDbType.Real;
                case DbType.Double:
                    return SqlDbType.Float;
                case DbType.Decimal:
                    return SqlDbType.Decimal;
                case DbType.DateTime:
                    return SqlDbType.DateTime;
                case DbType.Binary:
                    return SqlDbType.Image;
                case DbType.String:
                    return SqlDbType.NVarChar;
                case DbType.Guid:
                    return SqlDbType.UniqueIdentifier;
                default:
                    return SqlDbType.NText;
            }

        }
        #endregion


        #region  sql 2000 分页存储过程
        /*
     * 
     * CREATE  PROCEDURE [dbo].[GetRecordFromPage2000]
		(
		    @Table_Name               varchar(5000),      	    --表名
		    @Sign_Record              varchar(50),       		--主键
		    @Filter_Condition         varchar(1000),     	    --筛选条件,不带where
		    @Page_Size                int,               		--页大小
		    @Page_Index               int,          			--页索引     			
	        @TaxisField               varchar(1000),            --排序字段
		    @Taxis_Sign               int,               		--排序方式 1为 DESC, 0为 ASC
            @Find_RecordList          varchar(1000),        	--查找的字段
		    @Record_Count             int                		--总记录数
		 )
		 AS
			BEGIN 
			DECLARE  @Start_Number          int
			DECLARE  @End_Number            int
			DECLARE  @TopN_Number           int
		 DECLARE  @sSQL                  varchar(8000)
                 if(@Find_RecordList='')
                 BEGIN
                      SELECT @Find_RecordList='*'
                 END
		 SELECT @Start_Number =(@Page_Index-1) * @Page_Size
			IF @Start_Number<=0
		 SElECT @Start_Number=0
			SELECT @End_Number=@Start_Number+@Page_Size
			IF @End_Number>@Record_Count
		 SELECT @End_Number=@Record_Count
		 SELECT @TopN_Number=@End_Number-@Start_Number
		 IF @TopN_Number<=0
		 SELECT @TopN_Number=0
			print @TopN_Number
		 print @Start_Number
		 print @End_Number
		 print @Record_Count
                 IF @TaxisField=''
                 begin
                    select  @TaxisField=@Sign_Record
                 end
		 IF @Taxis_Sign=0
		  	BEGIN
		 		IF @Filter_Condition=''
				 BEGIN
		 			SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
		     			WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
		    			 WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
		    		 ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField
		 		END
				ELSE
				BEGIN
				SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
		     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
		     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
		     WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField
				 END
			END
		ELSE
			BEGIN
			IF @Filter_Condition=''
				BEGIN
					SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
		         WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
		         WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
		         ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'
		     END
			ELSE
			BEGIN
				SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
		     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
		     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
		     WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC'
		 END
			END
			EXEC (@sSQL)
			IF @@ERROR<>0
			RETURN -3              
		 RETURN 0
		 END
		 
		 PRINT  @sSQL
        GO

     * */


        #endregion

        #region SQL2005 分页存储过程
        /*
     * 
   CREATE PROCEDURE [dbo].[GetRecordFromPage2005] 
    @SelectList            VARCHAR(2000),    --欲选择字段列表
    @TableSource        VARCHAR(100),    --表名或视图表 
    @SearchCondition    VARCHAR(2000),    --查询条件
    @OrderExpression    VARCHAR(1000),    --排序表达式
    @PageIndex            INT = 1,        --页号,从0开始
    @PageSize            INT = 10        --页尺寸
AS 
BEGIN
    IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''
    BEGIN
        SET @SelectList = '*'
    END
    PRINT @SelectList
    
    SET @SearchCondition = ISNULL(@SearchCondition,'')
    SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))
    IF @SearchCondition <> ''
    BEGIN
        IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'
        BEGIN
            SET @SearchCondition = 'WHERE ' + @SearchCondition
        END
    END
    PRINT @SearchCondition

    SET @OrderExpression = ISNULL(@OrderExpression,'')
    SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))
    IF @OrderExpression <> ''
    BEGIN
        IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'
        BEGIN
            SET @OrderExpression = 'ORDER BY ' + @OrderExpression
        END
    END
    PRINT @OrderExpression

    IF @PageIndex IS NULL OR @PageIndex < 1
    BEGIN
        SET @PageIndex = 1
    END
    PRINT @PageIndex
    IF @PageSize IS NULL OR @PageSize < 1
    BEGIN
        SET @PageSize = 10
    END
    PRINT  @PageSize

    DECLARE @SqlQuery VARCHAR(4000)

    SET @SqlQuery='SELECT '+@SelectList+',RowNumber 
    FROM 
        (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber 
          FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource 
    WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR) 
    + ' AND ' + 
    CAST((@PageIndex * @PageSize) AS VARCHAR) 
--    ORDER BY ' + @OrderExpression
    PRINT @SqlQuery
    SET NOCOUNT ON
    EXECUTE(@SqlQuery)
    SET NOCOUNT OFF
 
    RETURN @@RowCount
END
     **/

        #endregion
    }
}
